package util;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.ArrayList;

/*数据库工具类
* */
@SuppressWarnings("all")
public class DBHelper {
    public Connection getConnection(){
        Connection con = null;
        try{
            Class.forName("com.mysql.cj.jdbc.Driver");
            //2.获得连接对象
            String url = "jdbc:mysql://localhost:3306/jdbc1?" +
                    "useSSL=false&useUnicode=true&char" +
                    "acterEncoding=UTF-8&serverTimezone" +
                    "=Asia/Shanghai&allowPublicKeyRetrieval=true";
            String username = "root";
            String password = "root";
            con = DriverManager.getConnection(url, username, password);
        } catch (ClassNotFoundException e) {
            throw new RuntimeException(e);
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return con;
    }


    //关闭数据库资源
    public void closeAll(Connection con,PreparedStatement ps,ResultSet rs){
        try{
            if(rs != null){
                rs.close();
            }
            if(ps != null){
                ps.close();
            }
            if(con != null){
                con.close();
            }
        } catch (SQLException e) {
            //throw new RuntimeException(e);
            e.printStackTrace();
        }

    }


    //关闭资源，最后都有一个close（）
    //数据库的增删改的通用方法
    public int dtUpdate(String sql,Object... obj){//3
        Connection con = getConnection();//1,2
        //调用第一个的连接方法
        PreparedStatement ps=null;
        int count = 0;
        try{
            ps = con.prepareStatement(sql);//4
            for(int i = 0;i<obj.length;i++){
                ps.setObject(i+1,obj[i]);//4
            }
            //5
            count = ps.executeUpdate();//5
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }finally{
            closeAll(con,ps,null);//7
        }
        return count;//6是返回，谁调谁处理
    }

    //连接数据库的查询
    //查询要返回集合 返回值是数据库查询结果返回的对象 list类型
    public ArrayList dtQuery(String sql, Class cla,Object...obj){//查多，用ArrayList
        ArrayList list=new ArrayList();
        //建立一个返回用的集合
        Connection con=getConnection();
        PreparedStatement ps = null;
        ResultSet rs = null;

        try {
            ps=con.prepareStatement(sql);
            for (int i = 0;i<obj.length;i++){
                ps.setObject(i+1,obj[i]);

            }
            rs =ps.executeQuery();
            while (rs.next()){
                Object o = cla.newInstance();//得到一个对象
                Field[] fields = cla.getDeclaredFields();//得到属性
                for(Field f:fields){//循环遍历属性
                    f.setAccessible(true);
                    f.set(o,rs.getObject(f.getName()));//得到属性，pname.age,sex
                }
                list.add(o);

            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } catch (InstantiationException e) {
            throw new RuntimeException(e);
        } catch (IllegalAccessException e) {
            throw new RuntimeException(e);
        }finally {
            closeAll(con,ps,rs);
        }
        return list;
    }

}











